package homework6

import org.apache.spark.sql.{DataFrame, SparkSession}

object SQLDemo {

  def main(args: Array[String]): Unit = {

    val spark = SparkSession
      .builder()
      .appName("SQLDemo")
      .master("local[*]")
      .getOrCreate()
    spark.sparkContext.setLogLevel("warn")

    import spark.implicits._

    Seq(
      (1, "2019-03-04", "2020-02-03"),
      (2, "2020-04-05", "2020-08-04"),
      (3, "2019-10-09", "2020-06-11")
    ).toDF("id", "startdate", "enddate").createOrReplaceTempView("a")

    //将行式数据转列式
    val df: DataFrame = spark.sql(
      """
        |select b.date from (
        |   select a.startdate date from a
        |   union all
        |   select a.enddate date from a
        |) as b order by date
        |""".stripMargin)
    println(df.collect().toBuffer)
    df.createOrReplaceTempView("b")

    //用序列函数求下一列数据
    val df2: DataFrame = spark.sql(
      """
        |select date as startdate, IFNULL(lead(date) over(order by date), date) as enddate
        |from b
        |""".stripMargin)
    println(df2.collect().toBuffer)

  }

}
